package fms.webservice.server.util;

import fms.webservice.server.common.AutoPosRequestXML;
import fms.webservice.server.common.WindRequestXML;
import org.apache.log4j.Logger;

/**
 * Created with IntelliJ IDEA.
 * User: root
 * Date: 14-7-9
 * Time: 上午9:32
 * To change this template use File | Settings | File Templates.
 *
 */
public class BuildSqlUtil {
    private static Logger logger = Logger.getLogger(BuildSqlUtil.class);
    private static String planTimeLimit = MqUtil.getConf("/sysBaseParamter.properties").getProperty("PlanTimeLimit");

    // 风温请求，从飞行计划中获取数据
    public static String buildSQL(WindRequestXML reqmsg) {
        if(reqmsg.getAircraftid()==null || reqmsg.getAircraftid().equals("")
                || reqmsg.getAlt()==null || reqmsg.getAlt().equals("")
                || reqmsg.getCptname()==null || reqmsg.getCptname().equals("")
                || reqmsg.getFlightID()==null || reqmsg.getFlightID().equals("")
                || reqmsg.getRequestdate()==null || reqmsg.getRequestdate().equals("")) {
            logger.info("请求xml缺少必要的数据!");
            return "";
        }
        String strSql = "SELECT * ";
        strSql += " FROM PLAN_CPT_WD t ";
        strSql += " WHERE 1 = 1 ";
        if (!ObjectUtil.isBlank(reqmsg.getFlightID())) {
            strSql += " AND t.airlines||t.flight_id='" + reqmsg.getFlightID() + "'";
        }
        if (!ObjectUtil.isBlank(reqmsg.getAlt())) {
            String[] altArray = reqmsg.getAlt().split("\\.");
            String alts = "";
            // 数据库中高度层单位是请求报中的高度层单位的100倍
            for (int i = 0; i < altArray.length; i++) {
                alts += "'" + altArray[i] + "00',";
            }
            alts = alts.substring(0, alts.length() - 1);
            strSql += " AND t.alt in (" + alts + ")";
        }
        if (!ObjectUtil.isBlank(reqmsg.getAircraftid())) {
            // .charAt(0)+ "-"+reqmsg.getAircraftid().substring(1)
            strSql += " AND t.aircraft_id ='" + reqmsg.getAircraftid() + "'";
        }
        if (!ObjectUtil.isBlank(reqmsg.getCptname())) {
            String[] cptArray = reqmsg.getCptname().split("\\.");
            String cptNames = "";
            for (int i = 0; i < cptArray.length; i++) {
                cptNames += "'" + cptArray[i] + "',";
            }
            cptNames = cptNames.substring(0, cptNames.length() - 1);
            strSql += " AND cpt_name in (" + cptNames + ") ";
        }
        String maxSql = strSql.replace("*", "max(t.plan_date)");

        strSql += " AND t.plan_date = (" + maxSql + ")  ";
        if (!ObjectUtil.isBlank(reqmsg.getRequestdate())) {
            strSql += " AND TO_NUMBER( TO_DATE('" + reqmsg.getRequestdate() + "','yyyy-mm-dd HH24:MI:SS') " +
                    "- TO_DATE(to_char(t.plan_date,'yyyy-MM-dd HH24:mi:ss'), 'yyyy-mm-dd HH24:MI:SS') ) <= "
                    + planTimeLimit;
        }
        logger.debug(strSql);
        return strSql;
    }

    // 飞机自动下发的航路点信息请求
    public static String buildSQL(AutoPosRequestXML reqmsg) {
        if(reqmsg.getAircraftid() == null || reqmsg.getAircraftid().equals("")
                ||reqmsg.getFlightID() == null ||reqmsg.getFlightID().equals("")
                ||reqmsg.getRequestdate() == null ||reqmsg.getRequestdate().equals("")){
            logger.info("请求xml缺少必要的数据!");
            return "";
        }
        String strSql = "SELECT * ";
        strSql += " FROM CPT_INFO t ";
        strSql += " WHERE 1 = 1 ";
        if (!ObjectUtil.isBlank(reqmsg.getAircraftid())) {
            strSql += " AND t.aircraft_id ='" + reqmsg.getAircraftid() + "'";
        }
        if (!ObjectUtil.isBlank(reqmsg.getFlightID())) {
            strSql += " AND ( t.airlines||t.flight_id='" + reqmsg.getFlightID() + "' )";
        }
        String maxSql = strSql.replace("*", "max(t.plan_date)");

        strSql += " AND t.plan_date = (" + maxSql + ")  ";
        if (!ObjectUtil.isBlank(reqmsg.getRequestdate())) {
            strSql += " AND TO_NUMBER( TO_DATE('" + reqmsg.getRequestdate() + "','yyyy-mm-dd HH24:MI:SS') " +
                    "- TO_DATE(to_char(t.plan_date,'yyyy-MM-dd HH24:mi:ss'), 'yyyy-mm-dd HH24:MI:SS') ) <= "
                    + planTimeLimit;
        }
        logger.debug(strSql);
        return strSql;
    }

    public static String buildValidateSql(String user, String password) {
        if(user==null||user.equals("")
                || password==null || password.equals(""))
            return "";
        String strSql = "SELECT * ";
        strSql += " FROM PLAN_USERS t ";
        strSql += " WHERE t.USERNAME = '"+user+"' ";
        strSql += " AND t.PASSWORD = '"+password+"' ";
        logger.debug(strSql);
        return strSql;
    }

//    public static void main(String[] args) {
////        String test = "100" ;
////        String[] arr = test.split("\\.");
//        System.out.println(planTimeLimit);
//    }
}

/* 调试SQL
SELECT *
FROM PLAN_CPT_WD t
WHERE 1 = 1
AND t.airlines||t.flight_id='9C8875'
AND t.alt in (30100, 27600)
AND t.aircraft_id ='B-1807'
AND cpt_name in ('ZSPD','awc','SMT')
AND t.plan_date = (
  SELECT max(t1.plan_date)
  FROM PLAN_CPT_WD t1
  WHERE 1 = 1
  AND t1.airlines||t1.flight_id='9C8875'
  AND t1.alt  in (30100, 27600)
  AND t1.aircraft_id ='B-1807'
  AND cpt_name in ('ZSPD','awc','SMT')
)
and TO_NUMBER( TO_DATE('2014-8-6 14:40:43', 'YYYY-MM-DD HH24:MI:SS')
- TO_DATE(to_char(t.plan_date,'yyyy-MM-dd HH24:mi:ss'), 'YYYY/MM/DD HH24:MI:SS') ) <= 2
*/
